package com.hutao.manage.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;



/**
 *
 * @author lhy
 *
 */
public class ReadExcel2003_2007 {
	private List<String[]> rows = new ArrayList<String[]>();
	private final OPCPackage xlsxPackage;
	private int minColumns;

	private class SheetToCSV implements SheetContentsHandler {
		private String[] record;
		private int minColumns;

		public SheetToCSV(int minColumns) {
			super();
			this.minColumns = minColumns;
		}

		@Override
		public void startRow(int rowNum) {
			record = new String[this.minColumns];
		}

		@Override
		public void endRow(int rowNum) {
			rows.add(this.record);
		}

		@Override
		public void cell(String cellReference, String formattedValue, XSSFComment comment) {
			int thisCol = (new CellReference(cellReference)).getCol();
			record[thisCol] = formattedValue;

		}

		@Override
		public void headerFooter(String text, boolean isHeader, String tagName) {
			// Skip, no headers or footers in CSV
		}

	}

	public ReadExcel2003_2007(OPCPackage pkg, int minColumns) {
		this.xlsxPackage = pkg;
		this.minColumns = minColumns;
	}

	public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler,
			InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
		DataFormatter formatter = new DataFormatter();
		InputSource sheetSource = new InputSource(sheetInputStream);
		try {
			XMLReader sheetParser = SAXHelper.newXMLReader();
			ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
			sheetParser.setContentHandler(handler);
			sheetParser.parse(sheetSource);
		} catch (ParserConfigurationException e) {
			throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
		}
	}

	public List<String[]> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
		ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
		XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
		StylesTable styles = xssfReader.getStylesTable();
		XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
		int index = 0;
		while (iter.hasNext()) {
			InputStream stream = iter.next();
			String sheetName = iter.getSheetName();
			processSheet(styles, strings, new SheetToCSV(this.minColumns), stream);
			stream.close();
			++index;
		}
		return this.rows;
	}

	/**
	 * 得到excel的记录
	 * 
	 * @param excelPath
	 * @param minColumns 输出多少列
	 * @return
	 * @throws Exception
	 */
	public static List<String[]> getRecords(String excelPath, int minColumns) throws Exception {
		File xlsxFile = new File(excelPath);
		if (!xlsxFile.exists()) {
			System.err.println("Not found or not a file: " + xlsxFile.getPath());
			return null;
		}
		OPCPackage p = OPCPackage.open(xlsxFile);
		ReadExcel2003_2007 xlsx2csv = new ReadExcel2003_2007(p, minColumns);
		List<String[]> list = xlsx2csv.process();
		p.close();
		return list;
	}

	public static List<Map<String, Object>> getRecordMapList(String path) throws Exception {
		// 需要的参数 是 excel文件的地址,和 一共有多少列
		List<String[]> list = getRecords(path, 642);
		// 如果需要数组里放数组
		List<Map<String, Object>> mapList = new ArrayList<>();
		Map<String, Object> map = null;
		for (int i = 1; i < list.size() - 1; i++) {
			int j = 0;
			map = new LinkedHashMap<>();
			for (String string : list.get(i)) {

				map.put("" + j + "", string);
				j++;
			}
			mapList.add(map);
		}
		return mapList;
	}

	public static List<String[]> getRecordArrList(String path) throws Exception {
		// 需要的参数 是 excel文件的地址,和 一共有多少列
		List<String[]> list = getRecords(path, 642);

		return list;
	}
	


    public static void main(String[] args) throws Exception {
		
		 List<String[]> newlist = new ArrayList<>();
		 List<String> md5List = new ArrayList<>();
		
		 List<String[]> list = new ArrayList<>();
		/* * List<String[]> sublist1 = getRecordArrList("D://1/hqmsts_201909-C.xlsx"); sublist1.remove(0);
		 * list.addAll(sublist1);
		 */
		List<Map<String,Object>> sublist2 = getRecordMapList("D://3.xls");
		for (Map<String,Object> map : sublist2) {
			System.out.println(map);
		}
		/*
		 * List<String[]> sublist3 = getRecordArrList("D://1/hqmsts_201909-D46.xlsx");
		 * sublist3.remove(0); list.addAll(sublist3);
		 * 
		 * List<String[]> sublist4 = getRecordArrList("D://1/hqmsts_201909-D47.xlsx");
		 * sublist4.remove(0); list.addAll(sublist4);
		 * 
		 * List<String[]> sublist5 = getRecordArrList("D://1/hqmsts_201909-Z51.xlsx");
		 * sublist5.remove(0); list.addAll(sublist5);
		 * 
		 * List<String[]> sublist6 = getRecordArrList("D://1/hqmsts_201909-Z85.xlsx");
		 * sublist6.remove(0); list.addAll(sublist6);
		 */
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		
		//System.out.println("原数据总数"+list.size());

		String md5 = null;
		List<Map<String, Object>> mapList = new ArrayList<>();
		Map<String, Object> map = null;
		for (String[] arrString : list) {
			// String str=new BigDecimal(arrString[16]).toString();
			System.out.println(arrString[4]);

			md5 = MD5.md5(arrString[2] + "-" + arrString[3]);
			if (md5List.contains(md5)) {
				continue;
			}
			md5List.add(md5);
			int j =0;
			map = new LinkedHashMap<>();
			for (String string :arrString) {

				map.put("" + j + "", string);
				j++;
			}
			
			System.out.println(map);
			mapList.add(map);
			
		
		}
		
		System.out.println("过滤数据总数"+newlist.size());
		
		
		
		
//		for (Map<String, Object> datamap : mapList) {
//			System.out.println(datamap);
//		}
		
		
//		String [] header = readCSV("D://1/hqmsts_201909-D45.csv");
		
		
		//ExcelUtil.exportExcel(out, title, header, mapList);
		
	}
    
    
    
	public static List<Map<String,Object>> getDataList() throws Exception {
		List<String[]> newlist = new ArrayList<>();
		List<String> md5List = new ArrayList<>();

		List<String[]> list = new ArrayList<>();
		List<String[]> sublist1 = getRecordArrList("D://1/hqmsts_201909-C.xlsx");
		sublist1.remove(0);
		list.addAll(sublist1);
		
		List<String[]> sublist2 = getRecordArrList("D://1/hqmsts_201909-D45.xlsx");
		sublist2.remove(0);
		list.addAll(sublist2);
		
		List<String[]> sublist3 = getRecordArrList("D://1/hqmsts_201909-D46.xlsx");
		sublist3.remove(0);
		list.addAll(sublist3);
		
		List<String[]> sublist4 = getRecordArrList("D://1/hqmsts_201909-D47.xlsx");
		sublist4.remove(0);
		list.addAll(sublist4);
		
		List<String[]> sublist5 = getRecordArrList("D://1/hqmsts_201909-Z51.xlsx");
		sublist5.remove(0);
		list.addAll(sublist5);
		
		List<String[]> sublist6 = getRecordArrList("D://1/hqmsts_201909-Z85.xlsx");
		sublist6.remove(0);
		list.addAll(sublist6);
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		// list.addAll(getRecordArrList(""));
		
		System.out.println("原数据总数"+list.size());

		String md5 = null;
		List<Map<String, Object>> mapList = new ArrayList<>();
		Map<String, Object> map = null;
		for (String[] arrString : list) {
			// String str=new BigDecimal(arrString[16]).toString();


			md5 = MD5.md5(arrString[2] + "-" + arrString[3]);
			if (md5List.contains(md5)) {
				continue;
			}
			md5List.add(md5);
			int j =0;
			map = new LinkedHashMap<>();
			for (String string :arrString) {

				map.put("" + j + "", string);
				j++;
			}
			
			mapList.add(map);
			
		
		}
		
		System.out.println("过滤数据总数"+newlist.size());
		
		
		return mapList;
		
//		for (Map<String, Object> datamap : mapList) {
//			System.out.println(datamap);
//		}
		
		
//		String [] header = readCSV("D://1/hqmsts_201909-D45.csv");
		
		
		//ExcelUtil.exportExcel(out, title, header, mapList);
		
		
	
		

	}
}
